#!/usr/bin/env python3
# -*- coding: utf-8 -*-
from typing import List

from fastapi import Query
from sqlalchemy.orm import Session
from sqlalchemy.sql.elements import and_

from backend.app.model import Article, ArticleTag, Tag
from backend.app.schema.article import CreateArticle, UpdateArticle


def get_article(db: Session) -> bool:
    return db.query(Article)


def get_articles(db: Session, page: int, limit: int) -> list:
    return db.query(Article).order_by(Article.date_time.desc()).limit(limit).offset((page - 1) * limit).all()


def get_article_title(db: Session, article_id: int) -> str:
    return db.query(Article).filter(Article.id == article_id).first().title


def get_articles_count(db: Session) -> int:
    return db.query(Article).count()


def get_articles_by_tag_id(db: Session, tag_id: int) -> list:
    article_tags = db.query(ArticleTag).filter(ArticleTag.tag_id == tag_id).all()
    data = []
    for article_tag in article_tags:
        data.append(
            db.query(Article).filter(Article.id == article_tag.article_id).order_by(Article.date_time.desc()).all())
    return data


def get_articles_by_tag_id_count(db: Session, tag_id: int) -> int:
    return db.query(ArticleTag).filter(ArticleTag.tag_id == tag_id).count()


def get_articles_by_category_id(db: Session, category_id: int, page: int, limit: int) -> list:
    return db.query(Article).filter(Article.category_id == category_id).order_by(Article.date_time.desc()). \
        limit(limit).offset((page - 1) * limit).all()


def get_articles_by_category_id_count(db: Session, category_id: int) -> int:
    return db.query(Article).filter(Article.category_id == category_id).count()


def get_hot(db: Session) -> list:
    return db.query(Article).order_by(Article.view_num.desc()).all()[0:3]


def get_new(db: Session) -> list:
    return db.query(Article).order_by(Article.date_time.desc()).all()[0:6]


def get_rank(db: Session) -> list:
    return db.query(Article).order_by(Article.view_num.desc()).all()[0:6]


def get_search(db: Session, key: str, page: int, limit: int) -> list:
    return db.query(Article).order_by(Article.date_time.desc()).filter(Article.title.like(f'%{key}%')).limit(
        limit).offset((page - 1) * limit).all()


def get_article_archive(db: Session) -> list:
    return db.query(Article.id, Article.title, Article.date_time).order_by(Article.date_time.desc()).all()


def get_article_for_id(db: Session, article_id: int) -> Article:
    return db.query(Article).get(article_id)


def get_article_by_id(db: Session, article_id: int) -> bool:
    return db.query(Article).filter(Article.id == article_id).first()


def create_article(db: Session, category_id: int, user_id: int, post: CreateArticle,
                   tag_id: List[int] = Query(...)) -> Article:
    article_obj = Article(**post.dict(), category_id=category_id, user_id=user_id)
    tags = []
    for tag in tag_id:
        tags.append(db.query(Tag).filter(Tag.id == tag).first())
    article_obj.tags = tags
    db.add(article_obj)
    db.commit()
    db.refresh(article_obj)
    return article_obj


def update_article(db: Session, article_id: int,  category_id: int, user_id: int, put: UpdateArticle,
                   tag_id: List[int] = Query(...)) -> bool:
    article = db.query(Article).filter(Article.id == article_id)
    article.update({
        'title': put.title,
        'content': put.content,
        'digest': put.digest,
        'view_num': put.view_num,
        'comment_num': put.comment_num,
        'picture': put.picture,
        'user_id': user_id,
        'category_id': category_id,
    })
    db.query(ArticleTag).filter(ArticleTag.article_id == article_id).delete()
    db.commit()
    for tag in tag_id:
        if not db.query(ArticleTag).filter(and_(ArticleTag.tag_id==tag, ArticleTag.article_id==article_id)).first():
            db.add(ArticleTag(article_id=article_id, tag_id=tag))
    db.commit()
    return article.first()


def delete_article(db: Session, article_id: int) -> bool:
    article = db.query(Article).filter(Article.id == article_id)
    article.delete()
    db.commit()
    return article.first()
